{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d731fcec-135e-4439-8630-c10a6217f8a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6ef354ca-c68c-4403-b477-49e0e5b23a83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库连接引擎创建成功！\n"
     ]
    }
   ],
   "source": [
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "88f819da-7f03-41f9-a31e-c1f29b28e6c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['序号', '所用时间', '1.您的性别○男性   ○女性', '2.您的年级○大一   ○大二   ○大三   ○大四',\n",
       "       '3.您的生源地○农村   ○城镇（乡镇）   ○地县级城市  ○省会城市及直辖市',\n",
       "       '4.您的月生活费○≦1,000元   ○1,001-2,000元   ○2,001-3,000元   ○≧3,001元',\n",
       "       '5.您进行过绿色低碳的相关生活方式吗?', '6.您觉得“低碳”，与你的生活关系密切吗？', '7.低碳生活是否会成为未来的主流生活方式？',\n",
       "       '8.您是否认为低碳生活会提高您的生活质量？', '9.您从以下哪种途径获取有关于“低碳”的信息？(电视广播传媒)',\n",
       "       '9 (专业环保机构)', '9 (报刊杂志)', '9(社交互动平台)', '9 (家人朋友同学)', '9 (学校宣传教育)',\n",
       "       '10.您认为以下哪些方法能提高绿色低碳意识? (加强低碳宣传力度)', '10 (加强低碳普及教育)', '10 (完善绿色低碳设施)',\n",
       "       '10(建立健全相关的政策法规)', '11.......会影响我对低碳生活的看法—家人亲友的实践', '身边的人的响应',\n",
       "       '周围的人身体力行', '舆论媒体积极倡导', '12.我认为低碳生活…—是明智的行为 ', '对大家都有好处', '能够减缓气候变暖',\n",
       "       '对解决环境问题是有益的', '13.—我有能力选择低碳生活', '我有条件实施低碳生活', '我有机会执行低碳生活',\n",
       "       '我有足够知识进行低碳生活', '14.我打算以后…—减少使用一次性产品', '合理处理生活中的废弃物', '在日常生活中会进行垃圾分类',\n",
       "       '尽可能劝说周围的人进行低碳生活', '15.日常生活中，我会......—对垃圾进行分类', '重复利用废旧物品', '避免使用一次性产品',\n",
       "       '劝说周围的人进行低碳生活', '低碳行为积极性'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=pd.read_excel('大学生低碳生活行为的影响因素数据集.xlsx')\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5967092b-a9ac-4723-a4ff-9e0a4f1a0d56",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1\n",
       "1        2\n",
       "2        3\n",
       "3        4\n",
       "4        6\n",
       "      ... \n",
       "313    403\n",
       "314    404\n",
       "315    406\n",
       "316    407\n",
       "317    408\n",
       "Name: 序号, Length: 318, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['序号']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e5aea538-111f-4edf-b680-7ac61d8a7672",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "318"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.to_sql(\n",
    "    name='low_carbon_lifestyle_survey',    # 数据库中的表名\n",
    "    con=engine,         # 数据库连接引擎\n",
    "    if_exists='append', # 如果表存在，追加数据；其他选项：'fail'-存在则报错, 'replace'-替换原表\n",
    "    index=True ,\n",
    "    schema=schema\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f689f301-a7ab-4a02-abf2-f35e74b61b2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "440"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.read_csv('medical_data.csv', encoding='gbk')\n",
    "df.to_sql(\n",
    "    name='medical_data',\n",
    "    con=engine,\n",
    "    index=True,\n",
    "    schema=schema,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a4d3ccd-0162-475d-ab46-fa7a358a3dda",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
